﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using Newtonsoft.Json;
using System.Configuration;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using Microsoft.JScript;

using Trade.DBUtility;
using Trade.DBUtility.DataTableName;



namespace TradeWeb.Controllers
{
    public class PlatFormController : Controller
    {
        //查询今日到帐[分页+模糊查询]
        public string GetCustMoneyListPages(string strUserCode, string strPageIndex, string strPageSizes, string strSearchWhere,string strTimeStart,string strTimeEnd)
        {
            string jsonResult = string.Empty;
            DataSet dsResult = null;
            string strSearchFilter = Microsoft.JScript.GlobalObject.decodeURIComponent(strSearchWhere);
            //时间类型也要格式化转码
            #region 格式化时间，取默认值
            strTimeStart = Microsoft.JScript.GlobalObject.decodeURIComponent(strTimeStart);
            strTimeEnd = Microsoft.JScript.GlobalObject.decodeURIComponent(strTimeEnd);
            if (string.IsNullOrEmpty(strTimeStart))
            {
                strTimeStart = "2016-01-01";
            }
            if (string.IsNullOrEmpty(strTimeEnd))
            {
                strTimeEnd = DateTime.Now.ToString("yyyy-MM-dd");
            }
            #endregion

            #region 验证接口参数
            try
            {
                DateTime dtimeStart = DateTime.Parse(strTimeStart);
                DateTime dtimeEnd = DateTime.Parse(strTimeEnd);
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"时间格式不正确！\"}";
                return jsonResult;
            }
            if (string.IsNullOrEmpty(strUserCode)) 
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"用户代码不能为空！\"}";
                return jsonResult;
            }
            #endregion

            int iPageIndex = 1;
            int iPageSize = 30;
            string strPageWhere = string.Empty;
            #region 分页条件
            if (!string.IsNullOrEmpty(strPageIndex))
                iPageIndex = System.Convert.ToInt32(strPageIndex) <= 0 ? 1 : System.Convert.ToInt32(strPageIndex);
            if (!string.IsNullOrEmpty(strPageSizes))
                iPageSize = System.Convert.ToInt32(strPageSizes) <= 0 ? 30 : System.Convert.ToInt32(strPageSizes);
            if (!(string.IsNullOrEmpty(strPageSizes) && string.IsNullOrEmpty(strPageIndex)))
            {
                strPageWhere = " colnum>(" + iPageIndex.ToString() + "-1)*" + iPageSize.ToString() + " AND colnum<=" + iPageIndex.ToString() + "*" + iPageSize.ToString() + "";
                strPageWhere = " Where " + strPageWhere;
            }
            #endregion

            string strWhere = " where ISDel=0 AND CONVERT(VARCHAR(10),Dates,20)>='" 
                    + strTimeStart + "' AND CONVERT(VARCHAR(10),Dates,20)<= '" + strTimeEnd + "' ";
            string strAndWhere = string.Empty;
            #region 模糊查询条件
            if (!string.IsNullOrEmpty(strSearchFilter))
            {
                string strParam1 = "@Param1";
                strAndWhere = string.Format("( SerialNum  LIKE '%'+{0}+'%' OR ClientName LIKE '%'+{1}+'%' OR CNDeptName  LIKE '%'+{2}+'%' OR CNUserName LIKE '%'+{3}+'%' )",
                        strParam1, strParam1,strParam1,strParam1);
                strWhere =strWhere + " and " + strAndWhere;
            }
            #endregion

            List<DataTableName> lstDTNData = new List<DataTableName>();
            DataTableName dtnTableName = null;
            
            dtnTableName = new DataTableName();
            dtnTableName.strSelectSQL = "SELECT * FROM (SELECT ROW_NUMBER() over(order by Dates DESC) as colnum,ISDel,SerialNum,Dates,DeptCode,CNDeptName,UserCode,CNUserName,Creater,CreaterName,ClientCode,ClientName,InvoiceNums,ExpPactNums,Payer,Curr,Moneys  FROM V_CustMoney" + strWhere + " ) tab_1 " + strPageWhere;
            dtnTableName.strTableName = "T_CustMoney";
            dtnTableName.SqlSelectParameterList.Clear();
            if (!string.IsNullOrEmpty(strSearchWhere))
            {
                dtnTableName.SqlSelectParameterList.Add(new SqlParameter("@Param1", strSearchFilter));
            }
            lstDTNData.Add(dtnTableName);
            //
            dtnTableName = new DataTableName();
            dtnTableName.strSelectSQL = "SELECT COUNT(*) Row_Count FROM V_CustMoney " + strWhere;
            dtnTableName.strTableName = "T_Count";
            dtnTableName.SqlSelectParameterList.Clear();
            if (!string.IsNullOrEmpty(strSearchWhere))
            {
                dtnTableName.SqlSelectParameterList.Add(new SqlParameter("@Param1", strSearchFilter));
            }
            lstDTNData.Add(dtnTableName);
            try
            {
                dsResult = CustomizableDesignBLL.GetData(lstDTNData, false);
                jsonResult = "{\"success\":true,\"data\":" + JsonConvert.SerializeObject(dsResult) + ",\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"查询数据失败！\"}";
                return jsonResult;
            }
        }

        //查询预警提醒列表[无分页和模糊查询]
        public string GetRemindList(string strUserCode)
        {
            string jsonResult = string.Empty;
            DataSet dsResult = new DataSet();

            //用户权限组
            List<string> lstManagerUser = new List<string>();
            string strUserPowerGroup = string.Empty;
            string strUserCodeList = string.Empty;
            string strManagerUserS = string.Empty;
            #region 获取用户权限UserPowerConditon
            DataSet dsUser = null;
            DataTableName dtnUser = new DataTableName();
            dtnUser.strSelectSQL = "select * from T_User where UserCode='" + strUserCode + "'";
            dtnUser.strTableName = "T_User";
            try
            {
                dsUser = CustomizableDesignBLL.GetData(dtnUser, false);
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"查询帐户信息失败！\"}";
                return jsonResult;
            }
            if (dsUser == null || dsUser.Tables.Count <= 0 || dsUser.Tables[0].DefaultView.Count <= 0)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"帐户信息不存在！\"}";
                return jsonResult;
            }

            bool bIsHighRole = false;
            string isHighPower = dsUser.Tables[0].DefaultView[0]["ISHighPower"].ToString();
            bIsHighRole = isHighPower == "是";

            DataSet dsAll = null;
            DataSet dsGroup = null;

            if (bIsHighRole)
            {
                DataTableName dtnAll = new DataTableName();
                dtnAll.strSelectSQL = "select * from T_User";
                dtnAll.strTableName = "T_All";
                try
                {
                    dsAll = CustomizableDesignBLL.GetData(dtnAll, false);
                    foreach (DataRowView drv in dsAll.Tables[0].DefaultView)
                    {
                        lstManagerUser.Add(drv["UserCode"].ToString().ToLower());
                    }
                }
                catch (Exception)
                {

                }
            }
            else
            {
                strUserPowerGroup = dsUser.Tables[0].DefaultView[0]["UserPowerGroup"].ToString();
                strUserCodeList = dsUser.Tables[0].DefaultView[0]["UserCodeList"].ToString();
                if (string.IsNullOrEmpty(strUserPowerGroup.Trim()))
                {
                    strManagerUserS = strUserCodeList;
                }
                else
                {
                    DataTableName dtnGroup = new DataTableName();
                    dtnGroup.strSelectSQL = "SELECT * FROM T_UserPowerGroup WHERE UserPowerGroup='" + strUserPowerGroup + "'";
                    dtnGroup.strTableName = "T_PowerGroup";
                    try
                    {
                        dsGroup = CustomizableDesignBLL.GetData(dtnGroup, false);
                    }
                    catch (Exception)
                    {

                    }
                    if (dsGroup == null || dsGroup.Tables.Count <= 0 || dsGroup.Tables[0].DefaultView.Count <= 0)
                    {
                        jsonResult = "{\"success\":false,\"data\":{},\"message\":\"查询用户组权限失败！\"}";
                        return jsonResult;
                    }
                    strManagerUserS = dsGroup.Tables[0].DefaultView[0]["UserCodeList"].ToString();
                    string[] aryList = strManagerUserS.Split('|');
                    foreach (string strItem in aryList)
                    {
                        if (strItem.Trim() == string.Empty)
                            continue;
                        lstManagerUser.Add(strItem);
                    }
                    if (lstManagerUser.IndexOf(strUserCode) < 0)
                        lstManagerUser.Add(strUserCode);
                }
            }
            #endregion

            //第1步：取出预警模块的SQL查询语句集、提醒文本集。
            DataSet dsModuleSQL = null;
            #region 查询预警模块的SQL查询语句的集合。[结果：SQL语句的列表]
            DataTableName dtnModule = new DataTableName();
            dtnModule.strSelectSQL = "SELECT * FROM T_SetRemind WHERE ISUse = 1 and '|'+cast(Reminer as varchar(8000))+'|' Like '%|'+@UserCode+'|%'";
            dtnModule.strTableName = "T_SetRemind";
            dtnModule.SqlSelectParameterList.Add(new SqlParameter("@UserCode", strUserCode.Trim()));
            try
            {
                dsModuleSQL = CustomizableDesignBLL.GetData(dtnModule, false);
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"查询模块分组信息失败！\"}";
                return jsonResult;
            }
            if (dsModuleSQL == null || dsModuleSQL.Tables.Count <= 0 || dsModuleSQL.Tables[0].DefaultView.Count <= 0)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"系统未查询到相关的模块分组信息！\"}";
                return jsonResult;
            }
            #endregion
            //数据集依据大分组排序
            dsModuleSQL.Tables[0].DefaultView.Sort = "RemindClass";

            //第2步：合成预警模块的查询语句列表；
            List<string> lstSQLFormat = new List<string>();
            List<string> lstTabNameFormat = new List<string>();
            #region 合成预警模块的查询语句列表
            string strTabNameFormat = "V_Remind";
            foreach (DataRowView drRow in dsModuleSQL.Tables[0].DefaultView)
            {
                string strUserPowerTEMP = "";
                for (int i = 0; i < lstManagerUser.Count; i++)
                {
                    if (strUserPowerTEMP == "")
                        strUserPowerTEMP = "'" + lstManagerUser[i] + "'";
                    else
                        strUserPowerTEMP = strUserPowerTEMP + ",'" + lstManagerUser[i] + "'";
                }
                string strSQLItem = drRow["RemindSQL"].ToString();
                if (strSQLItem.Trim() == "")
                    continue;
                strSQLItem = strSQLItem.ToLower().Replace("[UserCode]".ToLower(), "'" + strUserCode.Trim() + "'").Replace("[ManagerUserS]".ToLower(), strUserPowerTEMP);
                string strENTableNameTemp = strTabNameFormat + drRow["RemindName"].ToString();
                lstSQLFormat.Add(strSQLItem);
                lstTabNameFormat.Add(strENTableNameTemp);
            }
            #endregion

            //第三步：合成预警数据集，根据表名、预警查询语句获取各预警模块的单号，再合成预警数据集。
            DataSet dsModuleNumS = null;
            List<DataTableName> lstDTNModuleNums = new List<DataTableName>();
            DataTableName dtnModuleNum = null;
            #region 合成预警数据集
            if (lstSQLFormat.Count > 0)
            {
                for (int i = 0; i < lstSQLFormat.Count; i++)
                {
                    dtnModuleNum = new DataTableName();
                    dtnModuleNum.strSelectSQL = lstSQLFormat[i];
                    dtnModuleNum.strTableName = lstTabNameFormat[i];
                    //添加到DTN对象列表
                    lstDTNModuleNums.Add(dtnModuleNum);
                }
            }
            try
            {
                dsModuleNumS = CustomizableDesignBLL.GetData(lstDTNModuleNums, false);
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"合成预警数据集失败！\"}";
                return jsonResult;
            }
            if (dsModuleNumS == null || dsModuleNumS.Tables.Count <= 0)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"系统未查询到预警模块的数据集息！\"}";
                return jsonResult;
            }
            #endregion

            //第四步：合成最终的结果集合。
            string strTabNameResult = "V_Remind";
            DataTable dtRemind = new DataTable(strTabNameResult);
            dtRemind.Columns.Add(new DataColumn("RemindClass", typeof(string)));
            dtRemind.Columns.Add(new DataColumn("RemindText", typeof(string)));
            dtRemind.Columns.Add(new DataColumn("ModuleName", typeof(string)));
            dtRemind.Columns.Add(new DataColumn("PKColName", typeof(string)));
            dtRemind.Columns.Add(new DataColumn("PKValue", typeof(string)));
            dsResult.Tables.Add(dtRemind);
            #region 根据业务分组、模块、单号和提示信息，合成最后的结果集。
            try
            {
                List<string> strRemindClassList = new List<string>();
                for (int i = 0; i < dsModuleSQL.Tables[0].DefaultView.Count; i++)
                {
                    string strRemindClass = dsModuleSQL.Tables[0].DefaultView[i]["RemindClass"].ToString();
                    if (strRemindClassList.IndexOf(strRemindClass) < 0)
                    {
                        strRemindClassList.Add(strRemindClass);
                    }
                }
                int iRemindCount = 0;
                for (int i = 0; i < strRemindClassList.Count; i++)
                {
                    iRemindCount = 0;   //提醒记录数
                    DataRow[] drSelectRow = dsModuleSQL.Tables[0].Select("RemindClass='" + strRemindClassList[i] + "'");
                    foreach (DataRow drRow in drSelectRow)
                    {
                        int iStart;
                        int iEnd;
                        string strParam;
                        string strModuleName = drRow["ModuleName"].ToString();
                        string strPKColName = drRow["PKColName"].ToString();
                        string strRemindName = drRow["RemindName"].ToString();
                        foreach (DataTable dt in dsModuleNumS.Tables)
                        {
                            if (String.Compare(dt.TableName, strTabNameResult + strRemindName, true) != 0)
                                continue;
                            DataColumn dc = new DataColumn("RemindText", System.Type.GetType("System.String"));
                            dt.Columns.Add(dc);
                            foreach (DataRowView drvchild in dt.DefaultView)
                            {
                                DataRow drNew = dsResult.Tables[0].NewRow();
                                drNew["RemindClass"] = strRemindClassList[i];
                                drNew["ModuleName"] = strModuleName;
                                drNew["PKColName"] = strPKColName;
                                drNew["PKValue"] = drvchild[strPKColName].ToString();

                                //内容进行宏替换
                                int iIndexStart = 0;
                                string strSubRemindContent = drRow["RemindContent"].ToString();
                                string strRemindContent = drRow["RemindContent"].ToString();
                                while (strSubRemindContent.IndexOf("[") >= 0 && strSubRemindContent.IndexOf("]") >= 0)
                                {
                                    iStart = strRemindContent.IndexOf('[', iIndexStart);
                                    iEnd = strRemindContent.IndexOf(']', iStart);
                                    //替换
                                    strParam = strRemindContent.Substring(iStart + 1, iEnd - iStart - 1);
                                    strRemindContent = strRemindContent.Substring(0, iStart) + drvchild[strParam].ToString() + strRemindContent.Substring(iEnd + 1);
                                    //为处理单号中含有符号‘[’‘]’  这种情况  如果不处理就会白屏
                                    iEnd = iEnd - (strParam.Length + 2 - drvchild[strParam].ToString().Length);
                                    strSubRemindContent = strRemindContent.Substring(iEnd + 1);
                                    iIndexStart = iEnd + 1;
                                }
                                drNew["RemindText"] = strRemindContent;
                                drNew.EndEdit();
                                dsResult.Tables[0].Rows.Add(drNew);
                            }
                            iRemindCount = iRemindCount + dt.DefaultView.Count;
                        }
                    }
                }
                jsonResult = "{\"success\":true  ,\"data\":" + JsonConvert.SerializeObject(dsResult) + ",\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception)
            {
                jsonResult = "{\"success\":false,\"data\":{},\"message\":\"预警模块数据集分组失败！\"}";
                return jsonResult;
            }
            #endregion
        }

        #region 反射：利用反射，求出控制器中，接口方法的完整网络地址（包含地址栏参数）
        public string Method()
        {
            PlatFormController au = new PlatFormController();
            Type t = typeof(PlatFormController);
            string strControler = "PlatFormController";
            strControler = strControler.Replace("Controller", "").Trim();

            string jsonResult = "";
            List<string> lstResult = new List<string>();
            List<string> lstMethod = new List<string>();
            System.Collections.Hashtable hsParams = new System.Collections.Hashtable();


            System.Reflection.MethodInfo[] methods = t.GetMethods();
            for (int i = 0; i < methods.Length; i++)
            {
                System.Reflection.MethodInfo me = methods[i];
                string meName = me.Name;
                lstMethod.Add(meName);

                System.Reflection.ParameterInfo[] pars = me.GetParameters();
                for (int j = 0; j < pars.Length; j++)
                {
                    System.Reflection.ParameterInfo pa = pars[j];
                    string paName = pa.Name;
                    hsParams.Add(meName + j, paName);
                }
            }
            string strAddress1 = "localhost:5350/" + strControler + "/";
            for (int i = 0; i < lstMethod.Count; i++)
            {
                string strParas = "";
                string strMeName = lstMethod[i];
                foreach (System.Collections.DictionaryEntry item in hsParams)
                {
                    //KEY名称的长度仅比对应的方法的名称的长度多一位；（进行一次过滤；）
                    int iMeLength = strMeName.Length;
                    int iKeyLength = item.Key.ToString().Length;
                    bool bIsSure = iKeyLength - iMeLength == 1;

                    if (item.Key.ToString().Trim().Contains(strMeName.Trim()) && bIsSure)
                    {
                        strParas += item.Value.ToString() + "=&";
                    }
                }
                string strAddress = strAddress1 + strMeName + "?" + strParas;
                if (strAddress.EndsWith("&"))
                {
                    strAddress = strAddress.Substring(0, strAddress.Length - 1);
                }
                //正常情况下，我们创建的方法，会排序在类，在FrameWork中默认生成的方法的前面。（所以取最后一个自定义的方法名可以再次过滤；）
                if (strAddress.Contains("Method"))
                    break;
                lstResult.Add(strAddress);
            }
            jsonResult = JsonConvert.SerializeObject(lstResult);
            return jsonResult;
        }
        #endregion
    }
}
